﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.OleDb;
using KPIS.DBM;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DAL;

namespace KPIS.GERP.GBMS.DAL
{
    public sealed class ProjectRequestDAL : IProjectRequestDAL
    {
       
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public ProjectRequestDAL()
        {

        }

        public int Insert(ProjectInfo rqInfo)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(17);

                dbManager.AddParameters(0, "@code", rqInfo.Code);
                dbManager.AddParameters(1, "@name", rqInfo.Name);
                dbManager.AddParameters(2, "@descr", rqInfo.Description);
                dbManager.AddParameters(3, "@date", rqInfo.Date);
                dbManager.AddParameters(4, "@monthStart", rqInfo.MonthStart);
                dbManager.AddParameters(5, "@monthEnd", rqInfo.MonthEnd);
                dbManager.AddParameters(6, "@reason", rqInfo.Reason);
                dbManager.AddParameters(7, "@objects", rqInfo.Object);
                dbManager.AddParameters(8, "@target", rqInfo.Target);
                dbManager.AddParameters(9, "@plan", rqInfo.Plan);
                dbManager.AddParameters(10, "@descr1", rqInfo.AdditionalDetail);
                dbManager.AddParameters(11, "@remark", rqInfo.Remark);
                dbManager.AddParameters(12, "@strat_seq", rqInfo.Strategy);
                dbManager.AddParameters(13, "@job_site_seq", rqInfo.JobSite);
                dbManager.AddParameters(14, "@bgm_year", rqInfo.BgYear);
                dbManager.AddParameters(15, "@status", rqInfo.Status);
                dbManager.AddParameters(16, "@created_by", rqInfo.UserRecord.CreatedBy);
                
                strSQL = "INSERT INTO BGM_REQUEST(BGM_REQ_CODE, BGM_REQ_NAME, BGM_REQ_DESCR, BGM_REQ_DATE"
                        + ", BGM_REQ_MONTH_START, BGM_REQ_MONTH_END, BGM_REQ_REASON, BGM_REQ_OBJECT"
                        + ", BGM_REQ_TARGET, BGM_REQ_PLAN, BGM_REQ_REMARK, BGM_REQ_ADD_FIELD_1, BGM_STRAT_LIST_SEQ"
                        + ", JOB_SITE_SEQ, BGM_YEAR, RECORD_STATUS, CREATED_BY)"
                    + " VALUES(@code, @name, @descr, @date"
                        + ", @monthStart, @monthEnd, @reason, @objects, @target, @plan, @remark, @descr1, @strat_seq"
                        + ", @job_site_seq, @bgm_year, @status, @created_by)";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int Update(ProjectInfo rqInfo)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(19);

                dbManager.AddParameters(0, "@seq", rqInfo.Sequence);
                dbManager.AddParameters(1, "@code", rqInfo.Code);
                dbManager.AddParameters(2, "@name", rqInfo.Name);
                dbManager.AddParameters(3, "@descr", rqInfo.Description);
                dbManager.AddParameters(4, "@date", rqInfo.Date);
                dbManager.AddParameters(5, "@monthStart", rqInfo.MonthStart);
                dbManager.AddParameters(6, "@monthEnd", rqInfo.MonthEnd);
                dbManager.AddParameters(7, "@reason", rqInfo.Reason);
                dbManager.AddParameters(8, "@objects", rqInfo.Object);
                dbManager.AddParameters(9, "@target", rqInfo.Target);
                dbManager.AddParameters(10, "@plan", rqInfo.Plan);
                dbManager.AddParameters(11, "@descr1", rqInfo.AdditionalDetail);
                dbManager.AddParameters(12, "@remark", rqInfo.Remark);
                dbManager.AddParameters(13, "@strat_seq", rqInfo.Strategy);
                dbManager.AddParameters(14, "@job_site_seq", rqInfo.JobSite);
                dbManager.AddParameters(15, "@bgm_year", rqInfo.BgYear);
                dbManager.AddParameters(16, "@status", rqInfo.Status);
                dbManager.AddParameters(17, "@updated_by", rqInfo.UserRecord.UpdatedBy);
                dbManager.AddParameters(18, "@updated_when", DateTime.Now);

                strSQL = "UPDATE BGM_REQUEST"
                        + " SET BGM_REQ_CODE = @code"
                            + ", BGM_REQ_NAME = @name"
                            + ", BGM_REQ_DESCR = @descr"
                            + ", BGM_REQ_MONTH_START = @monthStart"
                            + ", BGM_REQ_MONTH_END = @monthEnd"
                            + ", BGM_REQ_REASON = @reason"
                            + ", BGM_REQ_OBJECT = @objects"
                            + ", BGM_REQ_TARGET = @target"
                            + ", BGM_REQ_PLAN = @plan"
                            + ", BGM_REQ_REMARK = @remark"
                            + ", BGM_REQ_ADD_FIELD_1 = @descr1"
                            + ", BGM_STRAT_LIST_SEQ = @strat_seq"
                            + ", JOB_SITE_SEQ = @job_site_seq"
                            + ", RECORD_STATUS = @status"
                            + ", UPDATED_BY = @updated_by"
                            + ", UPDATED_WHEN = @updated_when"
                    + " WHERE BGM_REQ_SEQ = @seq";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int Delete(ProjectInfo rqInfo)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                //dbManager.AddParameters(0, "@seq", rqInfo.Sequence);
                //dbManager.AddParameters(1, "@status", rqInfo.RecordStatus);
                //dbManager.AddParameters(2, "@updated_by", rqInfo.UpdatedBy);
                //dbManager.AddParameters(3, "@updated_when", DateTime.Now);

                strSQL = "UPDATE BGM_REQUEST"
                    + " SET RECORD_STATUS = @status"
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = @updated_when"
                    + " WHERE BGM_REQ_SEQ = @seq";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public IList<ProjectInfo> Search(ProjectInfo rqCritiria)
        {
            List<ProjectInfo> pjInfo = new List<ProjectInfo>();

            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(17);

                dbManager.AddParameters(0, "@seq", rqCritiria.Sequence);
                dbManager.AddParameters(1, "@code", rqCritiria.Code);
                dbManager.AddParameters(2, "@name", rqCritiria.Name);
                dbManager.AddParameters(3, "@descr", rqCritiria.Description);
                dbManager.AddParameters(4, "@date", rqCritiria.Date);
                dbManager.AddParameters(5, "@monthStart", rqCritiria.MonthStart);
                dbManager.AddParameters(6, "@monthEnd", rqCritiria.MonthEnd);
                dbManager.AddParameters(7, "@reason", rqCritiria.Reason);
                dbManager.AddParameters(8, "@objects", rqCritiria.Object);
                dbManager.AddParameters(9, "@target", rqCritiria.Target);
                dbManager.AddParameters(10, "@plan", rqCritiria.Plan);
                dbManager.AddParameters(11, "@descr1", rqCritiria.AdditionalDetail);
                dbManager.AddParameters(12, "@remark", rqCritiria.Remark);
                dbManager.AddParameters(13, "@strat_seq", rqCritiria.Strategy);
                dbManager.AddParameters(14, "@job_site_seq", rqCritiria.JobSite);
                dbManager.AddParameters(15, "@bgm_year", rqCritiria.BgYear);
                dbManager.AddParameters(16, "@status", SystemInfo._DeleteRecordStatus);
                //dbManager.AddParameters(17, "@created_by", rqCritiria.UserRecord.CreatedBy);

                strSQL = "SELECT BGM_REQ_CODE, BGM_REQ_NAME, BGM_REQ_DESCR, BGM_REQ_DATE"
                        + ", '', ''"
                        + ", BGM_REQ_REASON, BGM_REQ_OBJECT, BGM_REQ_TARGET, BGM_REQ_PLAN"
                        //+ ", BGM_REQ_ADD_FIELD_1, BGM_REQ_REMARK, BGM_STRAT_LIST_SEQ, JOB_SITE_SEQ, BGM_YEAR"
                        + ", '', BGM_REQ_REMARK, BGM_STRAT_LIST_SEQ, JOB_SITE_SEQ, BGM_YEAR"
                        + ", BGM_REQ_SEQ, RECORD_STATUS"
                    + " FROM BGM_REQUEST RQ"
                    + " WHERE RECORD_STATUS not like @status"
                        + ((rqCritiria.BgYear > 0) ? " AND BGM_YEAR = @bgm_year" : "")
                        + ((rqCritiria.Sequence > 0) ? " AND BGM_REQ_SEQ = @seq" : "")
                        + ((rqCritiria.Code != null) ? " AND BGM_REQ_CODE = @code" : "")
                        + ((rqCritiria.Name != null) ? " AND BGM_REQ_NAME = @name" : "")
                        + ((rqCritiria.Description != null) ? " AND BGM_REQ_DESCR = @descr" : "")
                        //+ ((rqCritiria.Date != null) ? " AND BGM_REQ_DATE = @date" : "")
                        //+ ((rqCritiria.MonthStart != null) ? " AND BGM_REQ_MONTH_START = @monthStart" : "")
                        //+ ((rqCritiria.MonthEnd != null) ? " AND BGM_REQ_MONTH_END = @monthEnd" : "")
                        + ((rqCritiria.Reason != null) ? " AND BGM_REQ_REASON = @reason" : "")
                        + ((rqCritiria.Object != null) ? " AND BGM_REQ_OBJECT = @objects" : "")
                        + ((rqCritiria.Target != null) ? " AND BGM_REQ_TARGET = @target" : "")
                        + ((rqCritiria.Plan != null) ? " AND BGM_REQ_PLAN = @plan" : "")
                        //+ ((rqCritiria.AdditionalDetail != null) ? " AND BGM_REQ_ADD_FIELD_1 = @descr1" : "")
                        + ((rqCritiria.Remark != null) ? " AND BGM_REQ_REMARK = @remark" : "")
                        + ((rqCritiria.Strategy != null) ? " AND BGM_STRAT_LIST_SEQ = @strat_seq" : "")
                        + ((rqCritiria.JobSite != null) ? " AND JOB_SITE_SEQ = @job_site_seq" : "");
                        //+ ((rqCritiria.Request.RecordStatus.Length > 0) ? " AND RECORD_STATUS = @status" : "")
                        //+ ((rqCritiria.UserRecord.CreatedBy > 0) ? " AND CREATED_BY = @created_by" : "");

                DataTable dtb = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                foreach (DataRow dr in dtb.Rows)
                {
                    ProjectInfo pjInfos  = new ProjectInfo();
                    pjInfos.Sequence = Convert.ToInt32(dr["BGM_REQ_SEQ"]);
                    pjInfos.Code = Convert.ToString(dr["BGM_REQ_CODE"]);
                    pjInfos.Name = Convert.ToString(dr["BGM_REQ_NAME"]);
                    pjInfos.Description = Convert.ToString(dr["BGM_REQ_DESCR"]);
                    pjInfos.Date = Convert.ToDateTime(dr["BGM_REQ_DATE"]);
                    pjInfos.Reason = Convert.ToString(dr["BGM_REQ_REASON"]);
                    pjInfos.Object = Convert.ToString(dr["BGM_REQ_OBJECT"]);
                    pjInfos.Target = Convert.ToString(dr["BGM_REQ_TARGET"]);
                    pjInfos.Plan = Convert.ToString(dr["BGM_REQ_PLAN"]);
                    pjInfos.Remark = Convert.ToString(dr["BGM_REQ_REMARK"]);
                    pjInfos.Strategy = Convert.ToString(dr["BGM_STRAT_LIST_SEQ"]);
                    pjInfos.JobSite = Convert.ToString(dr["JOB_SITE_SEQ"]);
                    pjInfos.BgYear = Convert.ToInt32(dr["BGM_YEAR"]);
                    pjInfos.Status = Convert.ToString(dr["RECORD_STATUS"]);
                }
                //while (rdr.Read())
                //{
                //    ProjectInfo req = new ProjectInfo(rdr.GetInt32(15), rdr.GetString(0), rdr.GetString(1), 
                //                    (rdr.IsDBNull(2)) ? "" : rdr.GetString(2), rdr.GetDateTime(3), 
                //                    (rdr.IsDBNull(4)) ? "" : rdr.GetString(4), (rdr.IsDBNull(5)) ? "" : rdr.GetString(5), 
                //                    (rdr.IsDBNull(6)) ? "" : rdr.GetString(6), (rdr.IsDBNull(7)) ? "" : rdr.GetString(7), 
                //                    (rdr.IsDBNull(8)) ? "" : rdr.GetString(8), (rdr.IsDBNull(9)) ? "" : rdr.GetString(9), 
                //                    (rdr.IsDBNull(10)) ? "" : rdr.GetString(10), (rdr.IsDBNull(11)) ? "" : rdr.GetString(11), 
                //                    (rdr.IsDBNull(12)) ? "" : rdr.GetString(12), (rdr.IsDBNull(13)) ? "" : rdr.GetString(13), 
                //                     rdr.GetInt32(14), rdr.GetString(16), null);
                //    pjInfo.Add(req);
                //}

                return pjInfo;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public IList<TreeViewInfo> GetTreeViewControl(int year, int jobsite)
        {
            List<TreeViewInfo> pjInfo = new List<TreeViewInfo>();

            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(17);

                dbManager.AddParameters(0, "@year", year);
                dbManager.AddParameters(1, "@jobsite", jobsite);
                dbManager.AddParameters(2, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT BGM_REQ_SEQ, BGM_REQ_NAME"
                    + ", BGM_STRAT_LIST_SEQ, JOB_SITE_SEQ"
                    + " FROM BGM_REQUEST RQ"
                    + " WHERE RECORD_STATUS not like @status"
                        + ((year > 0) ? " AND BGM_YEAR = @year" : "")
                        + ((jobsite > 0) ? "AND JOB_SITE_SEQ = @jobsite" : "");

                DataTable dtb = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                foreach (DataRow dr in dtb.Rows)
                {
                    TreeViewInfo req = new TreeViewInfo();
                    pjInfo.Add(req);
                }
                //while (rdr.Read())
                //{
                //    TreeViewInfo req = new TreeViewInfo();
                //    pjInfo.Add(req);
                //}

                return pjInfo;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public DataSet GetProjectTotalBudget(int reqSeq)
        {
            string strSQL = "SELECT S.BGM_SOURCE_SEQ, S.BGM_SOURCE_NAME, COALESCE(SUM(COALESCE(BGM_REQ_EXP_QTY,1) * BGM_REQ_EXP_PRICE), 0) AS TOTAL_REQ"
                + " FROM BGM_REQUEST_EXPENSE R"
                    + " LEFT JOIN BGM_REQUEST Q ON R.BGM_REQ_SEQ = Q.BGM_REQ_SEQ"
                    + " LEFT JOIN BGM_MAS_SOURCE S ON R.BGM_SOURCE_SEQ = S.BGM_SOURCE_SEQ"
                + " WHERE Q.BGM_REQ_SEQ = @seq"
                    + " AND Q.RECORD_STATUS not like @status"
                + " GROUP BY S.BGM_SOURCE_SEQ, S.BGM_SOURCE_NAME"
                + " UNION ALL"
                + " SELECT S.BGM_SOURCE_SEQ, S.BGM_SOURCE_NAME, 0 AS TOTAL_REQ"
                + " FROM BGM_MAS_SOURCE S"
                + " WHERE BGM_SOURCE_SEQ NOT IN (SELECT DISTINCT BGM_SOURCE_SEQ FROM BGM_REQUEST_EXPENSE WHERE BGM_REQ_SEQ = @seq)"
                + " ORDER BY BGM_SOURCE_SEQ";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", reqSeq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                return dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
    }
}
